# -*- coding: utf-8 -*-
from this_site.models import *
from washing.models import *
from shop.models import *
from sto.models import *
import json
from django.http import HttpResponse
from django.conf import settings

def check_free_or_sum(client, or_sum=0):
    service = Journal.objects.filter(client=Client.objects.get(pk=client)).order_by('-datatime')
    count = service.count() % 10
    sum = 0
    for ser in service[or_sum:9]:
        sum += ser.sum
    sum /= count
    if count == 9:
        free = 1
    else:
        free = 0
    data = {
            'status': 'ok',
            'sum': sum,
            'free': free,
            'count': count,
            }
    return data

def report_day(date):
    date_start = datetime.datetime.combine(date, datetime.time(0,0,0))
    date_end = datetime.datetime.combine(date, datetime.time(23,59,59))
    receipts = Receipts.objects.filter(date__gte=date_start, date__lte=date_end)
    expenditures = Expenditures.objects.filter(date__gte=date_start, date__lte=date_end)
    journal = Journal.objects.filter(datatime__gte=date_start, datatime__lte=date_end)
    receipt_goods = ReceiptOfGoods.objects.filter(datetime__gte=date_start, datetime__lte=date_end)
    sale_goods = Sale.objects.filter(datetime__gte=date_start, datetime__lte=date_end) 
    sto_journal = STOJournal.objects.filter(datetime__gte=date_start, datetime__lte=date_end)     
    
    def get_sum(obj, **kwargs):
        result = 0
        for sum in obj.filter(**kwargs):
            try:
                if sum.free:
                    if not sum.client:
                        result += 0
                    elif check_free_or_sum(sum.client.id)['sum'] >= sum.sum:
                        result += 0
                    elif check_free_or_sum(sum.client.id)['sum'] < sum.sum:
                        result += sum.sum - check_free_or_sum(sum.client.id)['sum']
                    else:
                        result += 0 
                else:
                    result += sum.sum
            except:
                if getattr(sum, 'sum', False):
                    result += sum.sum
                else:
                    result += sum.get_sum()
        return result
    
    sum_journal = get_sum(journal, family=False, clearing=False, card=False)
    sum_receipts = get_sum(receipts) + get_sum(sale_goods) + get_sum(sto_journal) 
    sum_expenditures = get_sum(expenditures) + get_sum(receipt_goods)
    sum_card = get_sum(journal, card=True)
    salary = {}
    
    for jour in journal:
        staff_list = jour.staff.all()
        percent = 0.3
        if jour.free:
            percent = 0.15
        for staff in staff_list:
            try:
                salary[u'%s' % staff.fio] += jour.sum*percent/staff_list.count()
            except:
                salary[u'%s' % staff.fio] = jour.sum*percent/staff_list.count()
    salary = json.dumps(salary)
    day_report, created = DayReport.objects.get_or_create(
                           date=date_end.date())
    day_report.sum_expenditures=sum_expenditures
    day_report.sum_washing=sum_journal 
    day_report.sum_card=sum_card
    day_report.sum_receipts=sum_receipts 
    day_report.salary=salary
    day_report.save()
    return day_report

def xls_to_response(xls, fname):
    response = HttpResponse(mimetype="application/ms-excel")
    response['Content-Disposition'] = 'attachment; filename=%s' % fname
    xls.save(response)
    return response

def report_day_xls(date, date2 = None):
    import xlwt
    import xlrd
    
    font0 = xlwt.Font()
    font0.bold = True
    
    style0 = xlwt.XFStyle()
    style0.font = font0
    style0.borders = xlwt.Borders()
    style0.borders.bottom = 1
    style0.borders.top = 1
    style0.borders.left = 1
    style0.borders.right = 1
    
    style1 = xlwt.XFStyle()
    style1.font = xlwt.Font()
    style1.borders = xlwt.Borders()
    style1.borders.bottom = 1
    style1.borders.top = 1
    style1.borders.left = 1
    style1.borders.right = 1
    
    style2 = xlwt.XFStyle()
    style2.font = font0
    
    wb = xlwt.Workbook()
    
    date_start = datetime.datetime.combine(date, datetime.time(0,0,0))
    if not date2:
        ws = wb.add_sheet('%s' % date)
        date_end = datetime.datetime.combine(date, datetime.time(23,59,59))
    else:
        ws = wb.add_sheet(u'%s по %s' % (date, date2))
        date_end = datetime.datetime.combine(date2, datetime.time(23,59,59))
    receipts = Receipts.objects.filter(date__gte=date_start, date__lte=date_end)
    expenditures = Expenditures.objects.filter(date__gte=date_start, date__lte=date_end)
    journal = Journal.objects.filter(datatime__gte=date_start, datatime__lte=date_end)
    receipt_goods = ReceiptOfGoods.objects.filter(datetime__gte=date_start, datetime__lte=date_end)
    sale_goods = Sale.objects.filter(datetime__gte=date_start, datetime__lte=date_end)
    sto_journal = STOJournal.objects.filter(datetime__gte=date_start, datetime__lte=date_end) 
    
    rb = xlrd.open_workbook('%s/xls/day_temp.xls' % settings.STATIC_DOC_ROOT, formatting_info=True)
    sheet = rb.sheet_by_index(0)
    row = sheet.row_values(0)
    c_sum = {}
    i = 0
    for c_el in row:
        ws.write(0, i, c_el, style0)
        i += 1
    i = 1
    for record in journal:
        ws.write(i, 0, i, style1)
        ws.write(i, 1, record.datatime.time().strftime("%H:%M"), style1)
        if record.client: ws.write(i, 2, record.client.auto_number, style1)
        else: ws.write(i, 2, " ", style1)
        ws.write(i, 3, record.autotype.name, style1)
        ws.write(i, 4, record.get_service_str().replace("<br/>", ", "), style1)
        if record.free: ws.write(i, 5, 1, style1) 
        else: ws.write(i, 5, 0, style1)
        if record.family: ws.write(i, 6, 1, style1) 
        else: ws.write(i, 6, 0, style1)
        if record.clearing: ws.write(i, 7, 1, style1) 
        else: ws.write(i, 7, 0, style1)
        ws.write(i, 8, record.sum, style1)
        i += 1
    ws.write(i, 7, u'Итого', style0)
    ws.write(i, 8, xlwt.Formula("SUMPRODUCT((F2:F%d=0)*(G2:G%d=0)*(H2:H%d=0);I2:I%d)" % (i, i, i, i)), style1)
    c_sum.update({'+':i})
    i += 2
    
    def receipts_and_expenditures(obj, i = 0):
        ws.write(i, 0, u'Операция', style0)
        ws.write(i, 1, u'Подразделение', style0)
        ws.write(i, 2, u'Сотрудник', style0)
        ws.write(i, 3, u'Описание', style0)
        ws.write(i, 4, u'Сумма', style0)
        i += 1
        i_start = i
        
        for record in obj:
            ws.write(i, 0, record.transaction.name, style1)
            ws.write(i, 1, record.section.name, style1)
            if record.staff: ws.write(i, 2, record.staff.fio, style1) 
            else: ws.write(i, 2, " ", style1) 
            ws.write(i, 3, record.description, style1)
            ws.write(i, 4, record.sum, style1)
            i += 1 
        ws.write(i, 3, u'Итого', style0)
        ws.write(i, 4, xlwt.Formula("SUM(E%d:E%d)" % (i_start, i)), style1)
        return i + 2     
    
    if receipts:
        ws.write(i, 0, u'Приход', style2)
        i = receipts_and_expenditures(receipts, i + 1)
        c_sum.update({'+':i - 2})
    if expenditures:
        ws.write(i, 0, u'Расход', style2)
        i = receipts_and_expenditures(expenditures, i + 1)
        c_sum.update({'-': i - 2})
    
    def goods(obj, i = 0):
        ws.write(i, 0, u'Товар', style0)
        ws.write(i, 1, u'Сумма', style0)
        i += 1
        i_start = i
        for record in obj:
            ws.write(i, 0, record.get_movementofgoods_str().replace("<br/>", ", "), style1)
            ws.write(i, 1, record.get_sum(), style1)
            i += 1
        ws.write(i, 0, u'Итого', style0)
        ws.write(i, 1, xlwt.Formula("SUM(B%d:B%d)" % (i_start, i)), style1)
        return i + 2

    if receipt_goods:
        ws.write(i, 0, u'Магазин поступление товара', style2)
        i = goods(receipt_goods, i + 1)
        c_sum.update({'-': i - 2})
        
    if sale_goods:
        ws.write(i, 0, u'Магазин реализация товара', style2)
        i = goods(sale_goods, i + 1)
        c_sum.update({'+': i - 2})
        
    ws.write(i, 0, u'Зарплата', style2)
    i += 1
    i_start = i
    salary = {}
    for jour in journal:
        staff_list = jour.staff.all()
        percent = 0.3
        if jour.free:
            percent = 0.15
        for staff in staff_list:
            try:
                salary[u'%s' % staff.fio] += jour.sum*percent/staff_list.count()
            except:
                salary[u'%s' % staff.fio] = jour.sum*percent/staff_list.count()
    for name, sum in salary.items():
        ws.write(i, 0, name, style1)
        ws.write(i, 1, sum, style1)
        i += 1
    ws.write(i, 0, u'Итого з/п', style0)
    ws.write(i, 1, xlwt.Formula("SUM(B%d:B%d)" % (i_start, i)), style1)
    c_sum.update({'-': i})
    i += 2

    ws.write(i, 0, u'СТО', style2)
    i += 1
    i_start = i
    for obj in sto_journal:
        ws.write(i, 0, obj.get_service_str().replace("<br/>", ", "), style1)
        ws.write(i, 1, obj.client, style1)
        ws.write(i, 2, obj.sum, style1)
        i += 1
    ws.write(i, 0, u'Итого', style0)
    ws.write(i, 1, u' ', style1)
    ws.write(i, 2, xlwt.Formula("SUM(C%d:C%d)" % (i_start, i)), style1)
    i += 2

    ws.write(i, 0, u'Итог', style0)
    if date2:
#        sum = 0
#        for obj in DayReport.objects.filter(date__gte=date, date__lte=date2):
#            sum += obj.sum_total()
#        ws.write(i, 1, sum, style1)
        ws.write(i, 1, DayReport.objects.get(date=date2).sum_total(), style1)
    else:
        ws.write(i, 1, DayReport.objects.get(date=date).sum_total(), style1)
    return xls_to_response(wb, '%s_по_%s.xls' % (date, date_end.date()))